package cn.lanqiao.service.impl;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import cn.lanqiao.common.MyException;
import cn.lanqiao.entity.Pages;
import cn.lanqiao.entity.Student;
import cn.lanqiao.mapper.StudentMapper;
import cn.lanqiao.service.StudentService;
/**
 * 学生管理模块
 * @author yejun
 */
@Service
public class StudentServiceImpl implements StudentService {

	@Autowired
	private cn.lanqiao.mapper.StudentMapper studentMapper;
   
	@Override
	public List<Student> selectStudent() {
		return studentMapper.selectStudent();
	}
	@Transactional(readOnly = false,rollbackFor = Exception.class)
	@Override
	public boolean batchImport(String fileName, MultipartFile file) throws Exception {
		boolean notNull = false;
		List<Student> studentList = new ArrayList<>();
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new MyException("上传文件格式不正确");
		}
		boolean isExcel2003 = true;
		if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
			isExcel2003 = false;
		}
		InputStream is = file.getInputStream();
		Workbook wb = null;
		if (isExcel2003) {
			wb = new HSSFWorkbook(is);
		} else {
			wb = new XSSFWorkbook(is);
		}
		Sheet sheet = wb.getSheetAt(0);
		if(sheet!=null){
			notNull = true;
		}
		Student student;
		for (int r = 2; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
			System.out.println(sheet.getLastRowNum());
			Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
			if (row == null){
				continue;
			}

			//sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException

			student = new Student();

			if( row.getCell(0).getCellType() !=1){//循环时，得到每一行的单元格进行判断
				throw new MyException("导入失败(第"+(r+1)+"行,用户名请设为文本格式)");
			}

			String studentName = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值

			if(studentName == null || studentName.isEmpty()){//判断是否为空
				throw new MyException("导入失败(第"+(r+1)+"行,用户名未填写)");
			}


			row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
			String grade = row.getCell(1).getStringCellValue();


			if(grade==null || grade.isEmpty()){
				throw new MyException("导入失败(第"+(r+1)+"行,年级未填写)");
			}
			
			row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第三个单元格的值
			String major = row.getCell(2).getStringCellValue();


			if(major==null || major.isEmpty()){
				throw new MyException("导入失败(第"+(r+1)+"行,专业未填写)");
			}
			
			row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第四个单元格的值
			String studentClass = row.getCell(3).getStringCellValue();


			if(studentClass==null || studentClass.isEmpty()){
				throw new MyException("导入失败(第"+(r+1)+"行,班级未填写)");
			}
			row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第五个单元格的值
			String institute = row.getCell(4).getStringCellValue();


			if(institute==null || institute.isEmpty()){
				throw new MyException("导入失败(第"+(r+1)+"行,学院未填写)");
			}
			row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第六个单元格的值
			String sex = row.getCell(5).getStringCellValue();


			if(sex==null || sex.isEmpty()){
				throw new MyException("导入失败(第"+(r+1)+"行,性别未填写)");
				
			}
			row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第七个单元格的值
			String tel = row.getCell(6).getStringCellValue();


			if(tel==null || tel.isEmpty()){
				throw new MyException("导入失败(第"+(r+1)+"行,电话号码未填写)");
			}
			
			row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第三个单元格的值
			String email = row.getCell(7).getStringCellValue();


			if(email==null || email.isEmpty()){
				throw new MyException("导入失败(第"+(r+1)+"行,邮箱未填写)");
			}
			//完整的循环一次 就组成了一个对象
			student.setStudentName(studentName);
			student.setGrade(grade);
			student.setMajor(major);
			student.setStudentClass(studentClass);
			student.setInstitute(institute);
			student.setSex(sex);
			student.setTel(tel);
			student.setEmail(email);
			studentList.add(student);
		}
		for (Student studentResord : studentList) {
			String name = studentResord.getStudentName();
			int cnt = studentMapper.selectByName(name);
			if (cnt == 0) {
				studentMapper.addStudent(studentResord);
				System.out.println(" 插入 "+studentResord);
			} else {
				studentMapper.updateStudentByName(studentResord);
				System.out.println(" 更新 "+studentResord);
			}
		}
		return notNull;
	}
	//展示所有数据
	@Override
	public List<Student> getAllStudent() {
		return studentMapper.listAllStudent();
	}
	//分页
	@Override
	public Pages<Student> studentPage(int indexPage, int pageSize, String searchkey) {
		Pages<Student> page = new Pages<Student>();
		//当前页面indexPage
		page.setIndexPage(indexPage);
		//总记录数totalData
		int totalData = studentMapper.count(searchkey);
		page.setTotalData(totalData);
		//当前页面记录数pageSize;
		page.setPageSize(pageSize);
		//总页数totalPages
		int totalPages = totalData % pageSize ==0 ? totalData / pageSize : totalData/ pageSize + 1;
		page.setTotalPages(totalPages);
		
		Map<String,Object> map = new HashMap<String,Object>();
		int startindex = (indexPage -1) * pageSize;
		int endindex = pageSize;
		
		map.put("startindex", startindex);
		map.put("endindex", endindex);
		map.put("searchkey", searchkey);
		
		List<Student> data = studentMapper.studentPages(map);
		page.setPageData(data);
		
		return page;
	}
	//查看详情
	@Override
	public Student getStudentById(Integer studentId) {
		 return studentMapper.getStudent(studentId);	
	}
	//删除
	@Override
	public int deleteStudent(Integer studentId) {
		return studentMapper.delete(studentId);
	}
	//修改
	@Override
	public int updateStudent(Student student) {
		return studentMapper.update(student);
	}
	//添加
	@Override
	public int insertStudent(Student student) {
		return studentMapper.insert(student);
	}
	
	 @Override
	    public int updatePwd(Student student) {
	        return studentMapper.updatePwd(student);
	    }

	    @Override
	    public int add(Student student) {
	        return studentMapper.add(student);
	    }
	    

	    @Override
	    public Student findById(Integer studentId) {
	        return studentMapper.findById(studentId);
	    }

	    @Override
	    public int deleteById(Integer studentId) {
	        return studentMapper.deleteById(studentId);
	    }
		@Override
		public IPage<Student> PageStudent(Page<Student> page) {
			return studentMapper.PageStudent(page);
		}
	
	
	
	
}
